-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		TuyenVD
-- Create date: 3/16/2011
-- Description:	This stored procedure use to get an Employee record
-- =============================================
ALTER PROCEDURE [GetSingleEmployee]
(
@EmployeeID INT
)
AS
BEGIN
	IF (EXISTS (SELECT 1 FROM [Employee] WHERE [EmployeeID] = @EmployeeID AND [IsDelete]=0))
	BEGIN
		SELECT 
				e.[EmployeeID]
				, e.[FullName]
				, e.[DateOfBirth]
				, e.[IdentityCardNumber]
				, e.[Email]
				, e.[Gender]
				, e.[JoinedDate]
				, e.[Address]
				, e.IsDelete
				, e.[PhoneNumber]
				, e.[MobileNumber]
				, e.[Description]
				, et.[EmploymentTypeID]
				, et.[EmploymentType]
				, r.[RoleID]
				, r.[RoleName]
				, e.[UserName]
		FROM 
				[Employee] e
		INNER JOIN 
				[EmploymentType] et
		ON 
				e.[EmployeeID] = @EmployeeID AND et.[EmploymentTypeID] = e.[EmploymentTypeID] 
		INNER JOIN
				[Role] r
		ON 
				r.[RoleID] = e.[RoleID]

		SELECT 
				a.[AuthorizationID]
				, a.[Description]
		FROM
				[Authorization] a
		INNER JOIN
				[AccountAuthorization] aa
		ON
				aa.[AuthorizationID] = a.[AuthorizationID] AND aa.[EmployeeID] = @EmployeeID
	END
END

/*
USE WarehouseManagement
GO

EXEC [GetSingleEmployee] 1
*/